const { query, run } = require('../database/db');

// 创建订单
async function createOrder(userId, orderData) {
    const { address, payment_method } = orderData;
    
    // 获取购物车商品
    const cartItems = await query(`
        SELECT c.product_id, c.quantity, p.price 
        FROM cart c 
        JOIN products p ON c.product_id = p.id 
        WHERE c.user_id = ?
    `, [userId]);

    if (cartItems.length === 0) {
        throw new Error('购物车为空');
    }

    // 计算总金额
    const totalAmount = cartItems.reduce((total, item) => total + (item.quantity * item.price), 0);

    // 开始事务
    await run('BEGIN TRANSACTION');

    try {
        // 创建订单
        const orderResult = await run(
            'INSERT INTO orders (user_id, total_amount, address, payment_method) VALUES (?, ?, ?, ?)',
            [userId, totalAmount, address, payment_method]
        );
        const orderId = orderResult.lastID;

        // 创建订单项
        for (const item of cartItems) {
            await run(
                'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)',
                [orderId, item.product_id, item.quantity, item.price]
            );

            // 更新商品库存
            await run(
                'UPDATE products SET stock = stock - ? WHERE id = ?',
                [item.quantity, item.product_id]
            );
        }

        // 清空购物车
        await run('DELETE FROM cart WHERE user_id = ?', [userId]);

        // 提交事务
        await run('COMMIT');

        return orderId;
    } catch (error) {
        // 回滚事务
        await run('ROLLBACK');
        throw error;
    }
}

// 获取用户订单列表
async function getOrders(userId) {
    return await query(`
        SELECT o.*, 
               GROUP_CONCAT(p.name || ' x' || oi.quantity) as items
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN products p ON oi.product_id = p.id
        WHERE o.user_id = ?
        GROUP BY o.id
        ORDER BY o.created_at DESC
    `, [userId]);
}

// 获取订单详情
async function getOrderDetails(orderId) {
    return await query(`
        SELECT o.*, oi.quantity, oi.price,
               p.name, p.image_url
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN products p ON oi.product_id = p.id
        WHERE o.id = ?
    `, [orderId]);
}

// 更新订单状态
async function updateOrderStatus(orderId, status) {
    await run(
        'UPDATE orders SET status = ? WHERE id = ?',
        [status, orderId]
    );
}

// 获取所有订单（管理员功能）
async function getAllOrders() {
    return await query(`
        SELECT o.*, u.username,
               GROUP_CONCAT(p.name || ' x' || oi.quantity) as items
        FROM orders o
        JOIN users u ON o.user_id = u.id
        JOIN order_items oi ON o.id = oi.order_id
        JOIN products p ON oi.product_id = p.id
        GROUP BY o.id
        ORDER BY o.created_at DESC
    `);
}

module.exports = {
    createOrder,
    getOrders,
    getOrderDetails,
    updateOrderStatus,
    getAllOrders
}; 